package ru.reaperoq.schema

import kotlinx.serialization.Serializable
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.SqlExpressionBuilder.eq
import org.jetbrains.exposed.sql.`java-time`.CurrentDateTime
import org.jetbrains.exposed.sql.`java-time`.datetime
import org.jetbrains.exposed.sql.transactions.transaction
import ru.reaperoq.utils.dbQuery
import ru.reaperoq.utils.toEpochMillis
import ru.reaperoq.utils.toLocalDateTime
import java.time.LocalDateTime

object Components : Table("components") {
    val id: Column<Int> = integer("id").autoIncrement().index()
    val name: Column<String> = varchar("name", 50) // Процессор, ОЗУ, Жесткий диск и т.д.
    val type: Column<String> = varchar("type", 100) // Intel Core i7, DDR4, SSD и т.д.
    val specifications: Column<String> = text("specifications") // Тактовая частота, объем, скорость и т.д.
    val purchasedAt: Column<LocalDateTime> = datetime("purchased_at")
    val createdAt: Column<LocalDateTime> = datetime("created_at").defaultExpression(CurrentDateTime())
    val serialNumber: Column<String?> = varchar("serial_number", 512).uniqueIndex().nullable()

    val computerId = reference("computer_id", Computers.id, onDelete = ReferenceOption.SET_NULL).nullable()
    val locationId = reference("location_id", Locations.id, onDelete = ReferenceOption.SET_NULL)
    val createdBy = reference("created_by", Users.id, onDelete = ReferenceOption.NO_ACTION)
    val verifiedBy = reference("verified_by", Users.id, onDelete = ReferenceOption.NO_ACTION)
    val lastVerifiedAt = datetime("last_verified_at")

    override val primaryKey = PrimaryKey(id)
}

@Serializable
data class Component(
    val id: Int,
    val name: String,
    val type: String,
    val specifications: String,
    val purchasedAt: Long,
    val computerId: Int?,
    val locationId: Int,
    val serialNumber: String?,
    val createdBy: Int,
    val createdAt: Long,
    val verifiedBy: Int,
    val lastVerifiedAt: Long?
)

class ComponentsService(database: Database) {
    init {
        transaction(database) {
            SchemaUtils.create(Components)
        }
    }

    suspend fun create(
        createdBy: Int,
        name: String,
        type: String,
        specifications: String,
        purchasedAt: Long,
        computerId: Int?,
        locationId: Int,
        serialNumber: String?,
        verifiedBy: Int,
    ): Int = dbQuery {
        Components.insert {
            it[Components.name] = name
            it[Components.type] = type
            it[Components.specifications] = specifications
            it[Components.computerId] = computerId
            it[Components.purchasedAt] = purchasedAt.toLocalDateTime()
            it[Components.locationId] = locationId
            it[Components.serialNumber] = serialNumber
            it[Components.verifiedBy] = verifiedBy
            it[Components.createdBy] = createdBy
            it[lastVerifiedAt] = CurrentDateTime()
        }[Components.id]
    }

    suspend fun read(id: Int): Component? {
        return dbQuery {
            Components.select { Components.id eq id }
                .map {
                    Component(
                        it[Components.id],
                        it[Components.name],
                        it[Components.type],
                        it[Components.specifications],
                        it[Components.purchasedAt].toEpochMillis(),
                        it[Components.computerId],
                        it[Components.locationId],
                        it[Components.serialNumber],
                        it[Components.createdBy],
                        it[Components.createdAt].toEpochMillis(),
                        it[Components.verifiedBy],
                        it[Components.lastVerifiedAt].toEpochMillis()
                    )
                }
                .singleOrNull()
        }
    }

    suspend fun readBySerial(serialNumber: String): Component? {
        return dbQuery {
            Components.select { Components.serialNumber eq serialNumber }
                .map {
                    Component(
                        it[Components.id],
                        it[Components.name],
                        it[Components.type],
                        it[Components.specifications],
                        it[Components.purchasedAt].toEpochMillis(),
                        it[Components.computerId],
                        it[Components.locationId],
                        it[Components.serialNumber],
                        it[Components.createdBy],
                        it[Components.createdAt].toEpochMillis(),
                        it[Components.verifiedBy],
                        it[Components.lastVerifiedAt].toEpochMillis()
                    )
                }
                .singleOrNull()
        }
    }

    suspend fun readByComputer(computerId: Int): List<Component> {
        return dbQuery {
            Components.select { Components.computerId eq computerId }
                .map {
                    Component(
                        it[Components.id],
                        it[Components.name],
                        it[Components.type],
                        it[Components.specifications],
                        it[Components.purchasedAt].toEpochMillis(),
                        it[Components.computerId],
                        it[Components.locationId],
                        it[Components.serialNumber],
                        it[Components.createdBy],
                        it[Components.createdAt].toEpochMillis(),
                        it[Components.verifiedBy],
                        it[Components.lastVerifiedAt].toEpochMillis()
                    )
                }
        }
    }

    suspend fun readByLocation(locationId: Int): List<Component> {
        return dbQuery {
            Components.select { Components.locationId eq locationId }
                .map {
                    Component(
                        it[Components.id],
                        it[Components.name],
                        it[Components.type],
                        it[Components.specifications],
                        it[Components.purchasedAt].toEpochMillis(),
                        it[Components.computerId],
                        it[Components.locationId],
                        it[Components.serialNumber],
                        it[Components.createdBy],
                        it[Components.createdAt].toEpochMillis(),
                        it[Components.verifiedBy],
                        it[Components.lastVerifiedAt].toEpochMillis()
                    )
                }
        }
    }

    suspend fun update(
        id: Int,
        name: String,
        type: String,
        specifications: String = "",
        purchasedAt: Long,
        computerId: Int? = null,
        locationId: Int,
        lastVerifiedAt: Long? = null
    ) {
        println("TIME: $purchasedAt")
        dbQuery {
            Components.update({ Components.id eq id }) {
                it[Components.name] = name
                it[Components.type] = type
                it[Components.specifications] = specifications
                it[Components.computerId] = computerId
                it[Components.locationId] = locationId
                it[Components.purchasedAt] = purchasedAt.toLocalDateTime()
                it[verifiedBy] = verifiedBy
                lastVerifiedAt?.toLocalDateTime()?.let { date ->
                    it[Components.lastVerifiedAt] = date
                }
            }
        }
    }

    suspend fun verify(id: Int, verifiedBy: Int) {
        dbQuery {
            Components.update({ Components.id eq id }) {
                it[Components.verifiedBy] = verifiedBy
                it[lastVerifiedAt] = CurrentDateTime()
            }
        }
    }

    suspend fun delete(id: Int) {
        dbQuery {
            Components.deleteWhere { Components.id eq id }
        }
    }

    suspend fun readAll(): List<Component> = dbQuery {
        Components.selectAll()
            .map {
                Component(
                    it[Components.id],
                    it[Components.name],
                    it[Components.type],
                    it[Components.specifications],
                    it[Components.purchasedAt].toEpochMillis(),
                    it[Components.computerId],
                    it[Components.locationId],
                    it[Components.serialNumber],
                    it[Components.createdBy],
                    it[Components.createdAt].toEpochMillis(),
                    it[Components.verifiedBy],
                    it[Components.lastVerifiedAt].toEpochMillis()
                )
            }
    }

    suspend fun readTypes(): List<String> = dbQuery {
        Components.selectAll().map { it[Components.type] }.distinct()
    }

    suspend fun readByUser(userId: Int): List<Component> = dbQuery {
        Components.select { Components.createdBy eq userId }
            .map {
                Component(
                    it[Components.id],
                    it[Components.name],
                    it[Components.type],
                    it[Components.specifications],
                    it[Components.purchasedAt].toEpochMillis(),
                    it[Components.computerId],
                    it[Components.locationId],
                    it[Components.serialNumber],
                    it[Components.createdBy],
                    it[Components.createdAt].toEpochMillis(),
                    it[Components.verifiedBy],
                    it[Components.lastVerifiedAt].toEpochMillis()
                )
            }
    }
}